# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import openpyxl as openpyxl
import pymysql as pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


# class MysqlPipline:
#     def __init__(self):
#         self.connect = pymysql.connect(host='8.137.150.77', port=3303,
#                                        user='root', password='123456',
#                                        database='scrapy', charset='utf8mb4')
#         self.cursor = self.connect.cursor()
#         self.data = []
#         self.item = None
#
#     def open_spider(self, spider):
#         print('MysqlPipline:', '开始抓取。。。。！')
#
#
#     def close_spider(self, spider):
#         if len(self.data) > 0:
#             self._write_mysql_()
#         self.connect.close()
#
#
#     def process_item(self, item, spider):
#         title = item.get('title', '')
#         price = item.get('price', '')
#         deal_count = item.get('deal_count', '')
#         shop_name = item.get('shop_name', '')
#         location = item.get('location', '')
#         if len(title) == 2:
#             name = title[1]
#             self.data.append((shop_name, name, price, deal_count, location))
#             if len(self.data) == 48:
#                 self._write_mysql_()
#                 self.data.clear()
#             return item
#         name = title[0]
#         self.data.append((shop_name, name, price, deal_count, location))
#         if len(self.data) == 48:
#             self._write_mysql_()
#             self.data.clear()
#         return item
#
#
#     def _write_mysql_(self):
#         for self.item in self.data:
#             sql = 'select * from taobao where shop_name = %s'
#
#             check_data = self.cursor.execute(
#                 sql, self.item[0]
#             )
#             print(check_data)
#             if check_data == 0:
#                 print('data:', self.item)
#                 self.cursor.execute(
#                     'insert into taobao (shop_name, title, price, deal_count, location) values (%s, %s, %s, %s, %s)',
#                     self.item
#                 )
#                 self.connect.commit()
#             continue


class ExcelPipeline:
    # 创建excel表
    def __init__(self):
        self.name = None
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = 'obsbot'
        self.ws.append(('标题', '价格', '购买信息', '商铺'))

    def open_spider(self, spider):
        print('ExcelPipeline:', '开始抓取。。。。！')


    def close_spider(self, spider):
        self.wb.save('jd.xlsx')

    # 拿到数据，提取对应的值，存入表中
    def process_item(self, item, spider):
        title = item.get('title', '')
        price = item.get('price', 0)
        deal_count = item.get('deal_count', '').encode('utf-8')
        shop_name = item.get('shop_name', '')
        if len(title) == 2:
            name = title[1]
            self.ws.append((name, price[0], deal_count, shop_name))
            return item
        name = title[0]
        self.ws.append((name, price[0], deal_count, shop_name))
        return item
